<?php
/**
 *	[TWCMS] (C)2012 TongWang Inc.
 *	$Id: tool_database.php 2 2013-04-27 07:14:17Z nanown@qq.com $
 */
class tool_database extends admin{
	public $path;
	public $tables;
	public $Arr = array();

	function __construct() {
		$this->path = ADMNAME.'?mod=tool&action=database';
		parent::__construct();
		$this->main();
	}

	function main() {
		if(empty($_GET['do'])) {
			include load_tpl(__CLASS__);
		}elseif($_GET['do'] == 'sqlbackup') {
			$this->sqlbackup();
		}elseif($_GET['do'] == 'ajax_revert') {
			$this->ajax_revert();
		}elseif($_GET['do'] == 'ajax_del') {
			$this->delSqlFile();
		}elseif($_GET['do'] == 'batch_del') {
			$this->batchDel();
		}elseif($_GET['do'] == 'batch_optimize') {
			$this->batchOptimize();
		}elseif($_GET['do'] == 'batch_repair') {
			$this->batchRepair();
		}elseif($_GET['do'] == 'down') {
			$this->downZip();
		}elseif($_GET['do'] == 'ajax_filelist') {
			$this->ajax_filelist();
		}elseif($_GET['do'] == 'ajax_tablelist') {
			$this->ajax_tablelist();
		}
	}

	//下载备份文件
	function downZip() {
		$sqlname = strC($_GET, 'sqlname');
		$filedir = TWCMS_DATA.'backup'.D.$sqlname;
		$zipfile =  $filedir.'.zip';

		if(empty($sqlname) || preg_match('/\W/', $sqlname)) {
			echo '非法文件名';
		}elseif(file_exists($zipfile)) {
			@header('location:'.str_replace(TWCMS_ROOT, '', $zipfile));
		}else{
			if(function_exists('gzcompress')) {
				require base::load_sys_file('zip');
				$zip = new zip();
				if($zip->zipFile($filedir, $zipfile)) {
					@header('location:'.str_replace(TWCMS_ROOT, '', $zipfile));
				}else{
					echo '压缩为zip文件失败，请使用FTP下载吧';
				}
			}else{
				echo '服务器不支持压缩功能，请使用FTP下载吧';
			}
		}
	}

	//数据库信息
	function ajax_info() {
		$query = $this->db->query('SHOW TABLE STATUS');
		$table_num = $data_size = $index_size = $free_size = $table_rows = 0;
		$other_table_num = $other_data_size = $other_index_size = $other_free_size = $other_table_rows = 0;
		$table = $other_table = array();
		while($row = $this->db->fetch_array($query)) {
			if(substr($row['Name'], 0, strlen(TW_DBPRE)) == TW_DBPRE) {
				$table_num++;
				$data_size += $row['Data_length'];
				$index_size += $row['Index_length'];
				$free_size += $row['Data_free'];
				$table_rows += $row['Rows'];
				$row['Create_time'] = $row['Create_time'] ? $row['Create_time'] : 'Unknow';
				$row['Update_time'] = $row['Update_time'] ? $row['Update_time'] : 'Unknow';
				$row['Data_length'] = get_byte($row['Data_length']);
				$row['Index_length'] = get_byte($row['Index_length']);
				$row['Data_free'] = get_byte($row['Data_free']);
				$table[] = $row;
			}else{
				$other_table_num++;
				$other_data_size += $row['Data_length'];
				$other_index_size += $row['Index_length'];
				$other_free_size += $row['Data_free'];
				$other_table_rows += $row['Rows'];
				$row['Create_time'] = $row['Create_time'] ? $row['Create_time'] : 'Unknow';
				$row['Update_time'] = $row['Update_time'] ? $row['Update_time'] : 'Unknow';
				$row['Data_length'] = get_byte($row['Data_length']);
				$row['Index_length'] = get_byte($row['Index_length']);
				$row['Data_free'] = get_byte($row['Data_free']);
				$other_table[] = $row;
			}
		}

		$this->Arr = array(
			'table_num' => $table_num,
			'data_size' => get_byte($data_size),
			'index_size' => get_byte($index_size),
			'free_size' => get_byte($free_size),
			'table_rows' => $table_rows,
			'other_table_num' => $other_table_num,
			'other_data_size' => get_byte($other_data_size),
			'other_index_size' => get_byte($other_index_size),
			'other_free_size' => get_byte($other_free_size),
			'other_table_rows' => $other_table_rows,
			'table' => $table,
			'other_table' => $other_table
		);
	}

	//数据库备份
	function sqlbackup() {
		$size = empty($_GET['size']) ? 2 : intval($_GET['size']);
		$volume = intget('volume', 1);
		$startfrom = intget('startfrom');
		$tableid = intget('tableid');
		$filename = empty($_GET['filename']) ? date('ymdHis_').random(16,2) : str_preg($_GET['filename']);

		$sqldump = "# Identify: ".base64_encode(time().','.TWCMS_VERSION.','.$volume)."\n".
		"# <?exit();?>\n".
		"# Twcms Multi-Volume Data Dump Vol.$volume\n".
		"# Version: Twcms ".TWCMS_VERSION."\n".
		"# Time: ".date('Y-m-d H:i')."\n".
		"# Twcms: http://www.twcms.cn\n".
		"# --------------------------------------------------------\n\n";

		$offset = 300;
		$sizelimit = $size * 1000000;

		$backupdir = TWCMS_DATA.'backup'.D.$filename.D;
		$cachefile = $backupdir.'cachetableall.php';
		if($volume == 1 && file_exists($cachefile)) unlink($cachefile);
		if(file_exists($cachefile)) {
			$this->tables = include $cachefile;
		}else{
			$this->getTableAll();
		}

		$tablecount = count($this->tables);
		if($volume == 1) $sqldump .= $this->getTableStr();	

		while($tableid < $tablecount && strlen($sqldump) < $sizelimit) {
			$table = $this->tables[$tableid];
			$numrows = $offset;
			while(strlen($sqldump) < $sizelimit && $numrows == $offset) {
				$firstfield = $this->db->fetch_first('SHOW FULL COLUMNS FROM '.$table);

				if($firstfield['Extra'] == 'auto_increment') {
					$selectsql = 'SELECT * FROM '.$table.' WHERE '.$firstfield['Field'].'>'.$startfrom.' ORDER BY '.$firstfield['Field'].' LIMIT '.$offset;
				} else {
					$selectsql = 'SELECT * FROM '.$table.' LIMIT '.$startfrom.','.$offset;
				}

				$rows = $this->db->query($selectsql);
				$numrows = $this->db->num_rows($rows);
				while($row = $this->db->fetch_row($rows)) {
					$comma = '';
					$sqldump .= 'INSERT INTO '.$table.' VALUES(';
					foreach($row as $val) {
						$sqldump .= $comma.$this->db->S($val);
						$comma = ',';
					}
					$sqldump .= ");\n";

					if($firstfield['Extra'] == 'auto_increment') {
						$startfrom = $row[0];
					}else{
						$startfrom++;
					}

					if(strlen($sqldump) > $sizelimit) {
						break 3;
					}
				}
			}
			$sqldump .= "\n";
			if($numrows != $offset) {
				$startfrom = 0;
				$tableid++;
			}
		}

		$url = $msg = $pbr = '';
		$status = 2;
		if(!_mkdir($backupdir)) {
			$msg = '创建文件夹失败,请检查目录属性';
		}elseif(!file_exists($cachefile) && !f_write($cachefile, '<?php return '.var_export($this->tables, true).';')) {
			$msg = '写入缓存文件失败,请检查目录属性';
		}elseif(!f_write($backupdir.$volume.'.sql', $sqldump)) {
			$msg = '写入文件失败,请检查目录属性';
		}else{
			if($tableid < $tablecount) {
				$msg = '数据文件 '.$volume.'.sql 创建成功';
				$url =  $this->path.'&do=sqlbackup&size='.$size.'&volume='.++$volume.'&startfrom='.$startfrom.'&tableid='.$tableid.'&filename='.$filename;
				$status = 1;
				$pbr = floor($tableid/$tablecount*100);
			}else{
				unlink($cachefile);
				$msg = '备份完成！';
				$status = 0;
				$pbr = 100;
			}
		}
		exit('{"status":"'.$status.'","msg":"'.$msg.'","pbr":"'.$pbr.'","url":"'.$url.'"}');
	}

	//数据库还原
	function ajax_revert() {
		$sqlname = strC($_GET, 'sqlname');
		$volume = intget('volume', 1);
		$volumes = intget('volumes');

		$sqlfile = TWCMS_DATA.'backup'.D.$sqlname.D.$volume.'.sql';
		$cacheFile = TWCMS_CACHE.$this->auser['user'].'_sqlname.php';
		$status = 2;
		$msg = $pbr = $url = '';
		if(empty($sqlname) || preg_match('/\W/', $sqlname)) {
			$msg = '非法文件名';
		}elseif(!file_exists($sqlfile)) {
			$msg = '还原失败, '.$volume.'.sql 文件不存在';
		}elseif(!$identify = $this->getIdentify($sqlfile)) {
			$msg = '读取备份文件失败';
		}elseif(!f_write($cacheFile, $sqlname)) {
			$msg = '写入缓存文件名失败';
		}else{
			$sqlquery = splitsql(f_read($sqlfile));

			$querytrue = $queryfalse = 0;
			foreach($sqlquery as $sql) {
				if(trim($sql) != '') $this->db->query($sql) ? $querytrue++ : $queryfalse++;
			}
			
			if($volume < $volumes) {
				$msg = '还原 '.$volume.'.sql 完成 (ok:'.$querytrue.($queryfalse ? ' <font color=\'red\'>err:'.$queryfalse.'</font>' : '').')';
				$status = 1;
				$pbr = floor($volume/$volumes*100);
				$url = ADMNAME.'?mod=sqlrevert&sqlname='.$sqlname.'&volumes='.$volumes.'&volume='.++$volume;		
			}else{
				unlink($cacheFile);
				$msg = '还原完成！';
				$status = 0;
				$pbr = 100;
			}
		}
		exit('{"status":"'.$status.'","msg":"'.$msg.'","pbr":"'.$pbr.'","url":"'.$url.'"}');
	}

	//备份文件列表
	function filelist() {
		$backupdir = TWCMS_DATA.'backup'.D;
		$fileArr = array();
		if(is_dir($backupdir)) {
			if($dh = opendir($backupdir)) {
				while (($dir = readdir($dh)) !== false) {
					$sqldir = $backupdir.D.$dir;
					if(filetype($sqldir)=='dir' && $identify = $this->getIdentify($sqldir.D.'1.sql')) {
						$volumes = $sqlsize = 0;
						if($dh2 = opendir($sqldir)) {
							while (($file = readdir($dh2)) !== false) {
								if(filetype($sqldir.D.$file)=='file' && strrchr($file, '.')=='.sql') {
									$sqlsize += filesize($sqldir.D.$file);
									$volumes++;
								}
							}
							closedir($dh2);
						}
						$fileArr[] = array('name'=>$dir, 'createtime'=>date('Y-m-d H:i:s', $identify[0]), 'version'=>$identify[1], 'sqlsize'=>get_byte($sqlsize), 'volumes'=>$volumes);
					}
				}
				closedir($dh);
			}
		}
		return $fileArr;
	}

	//删除备份文件
	function delSqlFile() {
		$sqlname = strC($_POST, 'sqlname');
		if(empty($sqlname) || preg_match('/\W/', $sqlname)) {
			$msg = '非法文件名';
		}else{
			set_time_limit(0);
			if(@delDir(TWCMS_DATA.'backup'.D.$sqlname)) {
				$msg = '删除完成';
				$err = 0;
			}else{
				$msg = '删除失败';
				$err = 1;
			}
		}
		exit('{"err":"'.$err.'","msg":"'.$msg.'"}');
	}

	//批量删除备份文件
	function batchDel() {
		$err = 1;
		if(!empty($_POST['dirarr']) && is_array($_POST['dirarr'])) {
			$err = 0;
			foreach($_POST['dirarr'] as $sqlname) {
				if(!delDir(TWCMS_DATA.'backup'.D.$sqlname)) $err = 1;
			}
		}
		$msg = empty($err) ? '删除完成' : '删除失败';
		exit('{"err":'.$err.',"msg":"'.$msg.'"}');
	}

	//批量优化表
	function batchOptimize() {
		$err = 1;
		$msg = '优化表失败';
		if(isset($_POST['batcharr']) && is_array($_POST['batcharr'])) {
			$sql = $comma = '';
			foreach($_POST['batcharr'] as $val) {
				$sql .= $comma.'`'.$val.'`';
				$comma = ',';
			}
			if($this->db->query('OPTIMIZE TABLE '.$sql)) {
				$err = 0;
				$msg = '优化表完成';
			}
		}
		exit('{"err":'.$err.',"msg":"'.$msg.'"}');
	}

	//批量修复表
	function batchRepair() {
		$err = 1;
		$msg = '修复表失败';
		if(isset($_POST['batcharr']) && is_array($_POST['batcharr'])) {
			$sql = $comma = '';
			foreach($_POST['batcharr'] as $val) {
				$sql .= $comma.'`'.$val.'`';
				$comma = ',';
			}
			if($this->db->query('REPAIR TABLE '.$sql)) {
				$err = 0;
				$msg = '修复表完成';
			}
		}
		exit('{"err":'.$err.',"msg":"'.$msg.'"}');
	}

	//获取鉴定信息
	function getIdentify($file) {
		if(!is_file($file)) return false;
		if(@$fp = fopen($file, 'rb')) {
			$Identify = explode(',', base64_decode(preg_replace("/^# Identify: (\w+).*/s", "\\1", fgets($fp, 256))));
			fclose($fp);
			return $Identify;
		}
		return false;
	}

	//获取所有表名
	function getTableAll() {
		$arr = array();
		$query = $this->db->query('SHOW TABLE STATUS LIKE "'.str_replace('_', '\_', addslashes(TW_DBPRE)).'%"');
		while($data = $this->db->fetch_array($query)) {
			$arr[] = $data['Name'];
		}
		$this->tables = $arr;
	}

	//获取所有表名创建SQL
	function getTableStr() {
		$sqldump = '';
		$this->db->query('SET SQL_QUOTE_SHOW_CREATE=0');
		foreach($this->tables as $table) {
			$query = $this->db->query('SHOW CREATE TABLE '.$table);
			$data = $this->db->fetch_row($query);
			$data[1] = preg_replace("/ AUTO_INCREMENT=\d+/", '', $data[1]);
			if($this->db->version() >= '4.1') $data[1] = preg_replace("/CHARSET=.+/", 'CHARSET=utf8', $data[1]);
			else $data[1] = preg_replace("/TYPE\=(.+)/", 'ENGINE=\\1 DEFAULT CHARSET=utf8', $data[1]);

			$query = $this->db->query("SHOW TABLE STATUS LIKE '$table'");
			$tablestatus = $this->db->fetch_array($query);
			$sqldump .= 'DROP TABLE IF EXISTS '.$table.";\n".$data[1].($tablestatus['Auto_increment'] ? " AUTO_INCREMENT=$tablestatus[Auto_increment]" : '').";\n\n";
		}
		return $sqldump;
	}

	//ajax显示数据库备份列表
	function ajax_filelist() {
		$filelist = $this->filelist();
		if(empty($filelist)) {
			skyTips('您还没进行过数据库备份，为了您的网站数据安全，建议立即进行数据库备份');
		}else{
			echo '<div class="warning bnote"><i></i>执行还原时，请不要进行其他操作，否则可能还原失败；还原命令会以备份的数据覆盖当前数据库数据，请谨慎操作；还原时需要耗费一些时间，建议您在访问量低的时候进行此操作；备份文件默认存放在 twcms/data/backup 目录。</div>';
			echo '<table class="tb2" style="margin-top:10px"><tr class="tit"><th width="25"><input type="checkbox" id="check_all"></th><th width="120">操作</th><th>备份文件</th><th>时间</th><th>版本</th><th>大小</th><th>卷数</th></tr>';
			foreach($filelist as $row) {
				echo '<tr class="li"><td><input type="checkbox" name="chk_name"></td><td><a href="?mod=tool&action=database&do=down&sqlname='.$row['name'].'" target="_blank">[下载]</a><a class="sqlrev">[还原]</a><a class="sqldel">[删除]</a></td><td>'.$row['name'].'</td><td>'.$row['createtime'].'</td><td>'.$row['version'].'</td><td>'.$row['sqlsize'].'</td><td>'.$row['volumes'].'</td></tr>';
			}
			echo '</table>';
		}
	}

	//ajax显示数据表列表
	function ajax_tablelist() {
		$this->ajax_info();
		echo '<div class="tb_t">本站数据表</div><table class="tb2"><tr class="tit2"><th width="25"><input type="checkbox" class="table_all"></th><th>数据表名称</th><th>创建时间</th><th>更新时间</th><th>记录数</th><th>数据</th><th>索引</th><th>碎片</th></tr>';
		foreach($this->Arr['table'] as $row) {
			echo '<tr class="li"><td><input type="checkbox" name="chk_table"></td><td>'.$row['Name'].'</td><td>'.$row['Create_time'].'</td><td>'.$row['Update_time'].'</td><td>'.$row['Rows'].'</td><td>'.$row['Data_length'].'</td><td>'.$row['Index_length'].'</td><td>'.$row['Data_free'].'</td></tr>';
		}
		echo '<tr class="li"><th colspan="4">共计:'.$this->Arr['table_num'].'个数据表</th><th>'.$this->Arr['table_rows'].'</th><th>'.$this->Arr['data_size'].'</th><th>'.$this->Arr['index_size'].'</th><th>'.$this->Arr['free_size'].'</th></tr></table>';
		
		if(!empty($this->Arr['other_table'])) {
			echo '<div class="tb_t" style="margin-top:20px">其他数据表</div><table class="tb2"><tr class="tit2"><th width="25"><input type="checkbox" class="table_all"></th><th>数据表名称</th><th>创建时间</th><th>更新时间</th><th>记录数</th><th>数据</th><th>索引</th><th>碎片</th></tr>';
			foreach($this->Arr['other_table'] as $row) {
				echo '<tr class="li"><td><input type="checkbox" name="chk_table"></td><td>'.$row['Name'].'</td><td>'.$row['Create_time'].'</td><td>'.$row['Update_time'].'</td><td>'.$row['Rows'].'</td><td>'.$row['Data_length'].'</td><td>'.$row['Index_length'].'</td><td>'.$row['Data_free'].'</td></tr>';
			}
			echo '<tr class="li"><th colspan="4">共计:'.$this->Arr['other_table_num'].'个数据表</th><th>'.$this->Arr['other_table_rows'].'</th><th>'.$this->Arr['other_data_size'].'</th><th>'.$this->Arr['other_index_size'].'</th><th>'.$this->Arr['other_free_size'].'</th></tr></table>';
		}
	}
}